﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Web;
using System.IO;
using System.Threading;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Configuration;
using BE.SearchCriteria;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace BLL
{
    public class EstadisticasBLL : BaseBLL
    {
        [DllImport("user32.dll")]
        static extern uint GetWindowThreadProcessId(IntPtr hWnd, ref IntPtr ProcessId);

        public static string GenerarCuboPedidosZonaVend(CuboPedidosZonaVendSC pFiltroSC)
        {
            string wCuboPath = null;
            try
            {
                string wComando = string.Format("exec GetCuboPedidos {0}, {1}",
                                                pFiltroSC.IdCampaña.HasValue ? pFiltroSC.IdCampaña.ToString() : "null",
                                                pFiltroSC.IdZona.HasValue ? pFiltroSC.IdZona.ToString() : "null");
                wCuboPath = CrearCuboExcel(wComando, "PedidosZonaVend");

            }
            catch (Exception ex)
            {
                if (!HandleException(ex))
                    throw ex;                
            }

            return wCuboPath;

        }

        public static string GenerarCuboPedidosArticulos(CuboPedidosArticulosSC pFiltroSC)
        {
            string wCuboPath = null;
            try
            {
                string wComando = string.Format("exec GetCuboPedidosArticulos {0}, {1}, {2}",
                                                pFiltroSC.IdCampaña.HasValue ? pFiltroSC.IdCampaña.ToString() : "null",
                                                pFiltroSC.IdZona.HasValue ? pFiltroSC.IdZona.ToString() : "null",
                                                pFiltroSC.IdRubro.HasValue ? pFiltroSC.IdRubro.ToString() : "null"
                                                );
                wCuboPath = CrearCuboExcel(wComando, "PedidosArticulos");

            }
            catch (Exception ex)
            {
                if (!HandleException(ex))
                    throw ex;
            }

            return wCuboPath;
        }

        public static string GenerarCuboDevoluciones(CuboDevolucionesSC pFiltroSC)
        {
            string wCuboPath = null;
            try
            {
                string wComando = string.Format("exec GetCuboDevoluciones {0}, {1}, {2}",
                                                pFiltroSC.IdCampaña.HasValue ? pFiltroSC.IdCampaña.ToString() : "null",
                                                pFiltroSC.IdZona.HasValue ? pFiltroSC.IdZona.ToString() : "null",
                                                pFiltroSC.IdRubro.HasValue ? pFiltroSC.IdRubro.ToString() : "null"
                                                );
                wCuboPath = CrearCuboExcel(wComando, "Devoluciones");
            }
            catch (Exception ex)
            {
                if (!HandleException(ex))
                    throw ex;
            }

            return wCuboPath;
        }


        private static string CrearCuboExcel(string pComando, string pTemplateFile)
        {            
            // Se crea directorio de trabajo temporal.
            if (!Directory.Exists(HttpContext.Current.Server.MapPath("~/Cubos/TempFiles")))
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Cubos/TempFiles"));

            string wCuboTemplatePath = HttpContext.Current.Server.MapPath(string.Format("~/Cubos/{0}.xlsx", pTemplateFile));
            string wCuboPath = HttpContext.Current.Server.MapPath(string.Format("~/Cubos/TempFiles/{0}.{1}.xlsx", pTemplateFile, DateTime.Now.ToString("yyMMddHHmmss")));

            if (File.Exists(wCuboPath))
                File.Delete(wCuboPath);
            // Hacer una copia del template, para poder manipularlo sin problemas
            File.Copy(wCuboTemplatePath, wCuboPath, true);
            // Quitar readonly
            FileAttributes attributes = File.GetAttributes(wCuboPath);
            if ((attributes & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
            {                
                attributes = RemoveAttribute(attributes, FileAttributes.ReadOnly);
                File.SetAttributes(wCuboPath, attributes);                
            }
            
            Excel.Application oApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet oSheet = null;
            Excel.PivotTables pivotTables = null;
            try
            {
                oApp = new Excel.Application();
                oApp.DisplayAlerts = false;

                // Abrir el archivo de Excel
                excelWorkbook = oApp.Workbooks.Open(wCuboPath,
                                            0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                            true, false, 0, true, false, false);
                // Referenciar a los datos de la tabla pivot.
                Excel.PivotCache pivotCache = excelWorkbook.PivotCaches().Item(1);
                // Setear la conexión y el comando
                string wConnectionTemplate = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source={0};Initial Catalog={1}";                
                SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["VentaPorCatalogo"].ConnectionString);
                wConnectionTemplate = string.Format(wConnectionTemplate, sqlBuilder.DataSource, sqlBuilder.InitialCatalog);
                pivotCache.Connection = wConnectionTemplate;
                pivotCache.MaintainConnection = true;
                pivotCache.CommandText = pComando;
                pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
                // Refrescar la tabla
                oSheet = excelWorkbook.ActiveSheet;
                pivotTables = (Excel.PivotTables)oSheet.PivotTables(Type.Missing);
                pivotTables.Item(1).RefreshTable();
                // Guardar los cambios
                Thread.Sleep(300);
                excelWorkbook.Save();
                //excelWorkbook.SaveAs(@"d:\modificado.xlsx", Type.Missing, Type.Missing, Type.Missing,
                //                    Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
                //                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                oApp.Quit();

            }
            finally
            {
                // Cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();
                if (oSheet != null)
                    Marshal.FinalReleaseComObject(oSheet);
                if (pivotTables != null)
                    Marshal.FinalReleaseComObject(pivotTables);
                if (excelWorkbook != null)
                    Marshal.FinalReleaseComObject(excelWorkbook);
                if (oApp != null)
                {
                    Kill(oApp.Hwnd);
                    Marshal.FinalReleaseComObject(oApp);
                    oApp = null;
                }                
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            
            return wCuboPath;
        }

    
    private static FileAttributes RemoveAttribute(FileAttributes attributes, FileAttributes attributesToRemove)
    {
        return attributes & ~attributesToRemove;
    }
        private static void Kill(int hwnd)
        {
            int excelPID = 0;
            int handle = hwnd;
            IntPtr ptrExcel = new IntPtr(excelPID);
            GetWindowThreadProcessId(new System.IntPtr(handle), ref ptrExcel);

            excelPID = ptrExcel.ToInt32();
            Process process = null;
            try
            {
                process = Process.GetProcessById(excelPID);
                //
                // If we found a matching Excel proceess with no main window
                // associated main window, kill it.
                //
                if (process != null)
                {
                    if (process.ProcessName.ToUpper() == "EXCEL" && !process.HasExited)
                        process.Kill();
                }
            }
            catch { }
        }

    }
}
